package com.mililoan.persist.system.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.mililoan.common.bean.PageResultSet;
import com.mililoan.common.dao.AbstractBaseDao;
import com.mililoan.common.dao.JdbcPageResultSetDao;
import com.mililoan.common.utils.StackTraceUtil;
import com.mililoan.model.system.Param;
import com.mililoan.persist.system.ParamDao;

/**
 * 参数表DAO接口
 * @author taochen
 * @version 1.0
 * @since 2017-08-26
 */
@Repository("paramDao")
public class ParamDaoImpl extends AbstractBaseDao<Param> implements ParamDao {
	private static final Logger LOGGER = LoggerFactory.getLogger(ParamDaoImpl.class);
	private static final String SELECT_SQL = "SELECT T.ID,T.PARAM_NAME,T.PARAM_KEY,T.PARAM_VALUE,T.FLAG,T.DESCRIPTION FROM sys_param T";
	
	public Long add(final Param param) throws Exception{
		JdbcTemplate template = this.getJdbcTemplate();
		Long newId = new Long(0);
		final String sql = "INSERT INTO sys_param(PARAM_NAME,PARAM_KEY,PARAM_VALUE,FLAG,DESCRIPTION" 					
			+ ") VALUES (?,?,?,?,?)";
		
		try {
			//返回主键
			KeyHolder keyHolder = new GeneratedKeyHolder();
			template.update(new  PreparedStatementCreator(){
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {					
					PreparedStatement ps = con.prepareStatement(sql, 
								PreparedStatement.RETURN_GENERATED_KEYS);
							ps.setString(1, param.getParamName());
							ps.setString(2, param.getParamKey());
							ps.setString(3, param.getParamValue());
							ps.setString(4, param.getFlag());
							ps.setString(5, param.getDescription());
					return ps;
				}
			},keyHolder);
			//获取主键
			newId = keyHolder.getKey().longValue();
		} catch (Exception e) {			
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return newId;	
	}
	
	public Long update(final Param param) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		String sql = "UPDATE sys_param T SET T.PARAM_NAME=?,T.PARAM_KEY=?,T.PARAM_VALUE=?,T.FLAG=?,T.DESCRIPTION=?"
				+ " WHERE T.ID=?";
		try {
			template.update(sql, new PreparedStatementSetter() {			
				public void setValues(PreparedStatement ps) throws SQLException {
						ps.setString(1, param.getParamName());
						ps.setString(2, param.getParamKey());
						ps.setString(3, param.getParamValue());
						ps.setString(4, param.getFlag());
						ps.setString(5, param.getDescription());
						ps.setLong(6, param.getId());
				}
			});
		}
		catch (DataAccessException e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);		
		}
		return param.getId();
	}
	
	public Long delete(Long keyId) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		try {
//			String sql = "DELETE FROM sys_param WHERE ID=?";
			String sql = "UPDATE sys_param T SET T.FLAG='0' WHERE T.ID=?";
			template.update(sql, new Object[] { keyId });
		} catch (DataAccessException e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return keyId;
	}
	
	public PageResultSet<Param> queryParamList(Param param, int page, int pageSize) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		ArrayList<Object> rag = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(SELECT_SQL + " WHERE 1=1");				
		if(!StringUtils.isEmpty(param.getParamName())){
			sql.append(" AND T.PARAM_NAME = ?");
			rag.add(param.getParamName());
		}
		if(!StringUtils.isEmpty(param.getParamKey())){
			sql.append(" AND T.PARAM_KEY = ?");
			rag.add(param.getParamKey());
		}
		if(!StringUtils.isEmpty(param.getParamValue())){
			sql.append(" AND T.PARAM_VALUE = ?");
			rag.add(param.getParamValue());
		}
		if(!StringUtils.isEmpty(param.getFlag())){
			sql.append(" AND T.FLAG = ?");
			rag.add(param.getFlag());
		}
		if(!StringUtils.isEmpty(param.getDescription())){
			sql.append(" AND T.DESCRIPTION = ?");
			rag.add(param.getDescription());
		}
		final JdbcPageResultSetDao<Param> pageDao = new JdbcPageResultSetDao<Param>(pageSize);
		pageDao.setPage(page);
		final List<Param> pagelist = pageDao.getPrs().getResult();
		PageResultSet<Param> l_pageResultSet = null;
		try {
			l_pageResultSet = pageDao.pageResultSet(sql.toString(), template, rag.toArray(), new RowCallbackHandler() {	
				public void processRow(ResultSet rs) throws SQLException {
					pagelist.add(buildParam(rs));
				}			
			});
		} catch (Exception e) {			
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return l_pageResultSet;
	}
	
	@SuppressWarnings({"unchecked", "rawtypes"})
	public Param getParamById(Long keyId) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		Param param = new Param();
		String sql = new String(SELECT_SQL + " WHERE T.ID = ?");
		
		try {
			param = (Param)template.queryForObject(sql, new Object[] { keyId },
					new RowMapper() {
						public Object mapRow(ResultSet rs, int rowNum) throws SQLException {						
						return buildParam(rs);
					}
				}
			);
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return param;
	}
	
	private Param buildParam(ResultSet rs) {
	 	Param rsParam = new Param();
	 	try {
			rsParam.setId(rs.getLong("ID"));
			rsParam.setParamName(rs.getString("PARAM_NAME"));
			rsParam.setParamKey(rs.getString("PARAM_KEY"));
			rsParam.setParamValue(rs.getString("PARAM_VALUE"));
			rsParam.setFlag(rs.getString("FLAG"));
			rsParam.setDescription(rs.getString("DESCRIPTION"));
		} catch (Exception e) {
            LOGGER.error(StackTraceUtil.getStackTrace(e));
        }
		return rsParam;
	 }

	public List<Param> queryParamAllList(Param param) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		ArrayList<Object> rag = new ArrayList<Object>();
		final ArrayList<Param> paramList= new ArrayList<Param>();
		
		StringBuffer sql = new StringBuffer(SELECT_SQL + " WHERE 1=1");				
		if(!StringUtils.isEmpty(param.getParamName())){
			sql.append(" AND T.PARAM_NAME = ?");
			rag.add(param.getParamName());
		}
		if(!StringUtils.isEmpty(param.getParamKey())){
			sql.append(" AND T.PARAM_KEY = ?");
			rag.add(param.getParamKey());
		}
		if(!StringUtils.isEmpty(param.getParamValue())){
			sql.append(" AND T.PARAM_VALUE = ?");
			rag.add(param.getParamValue());
		}
		if(!StringUtils.isEmpty(param.getFlag())){
			sql.append(" AND T.FLAG = ?");
			rag.add(param.getFlag());
		}
		if(!StringUtils.isEmpty(param.getDescription())){
			sql.append(" AND T.DESCRIPTION = ?");
			rag.add(param.getDescription());
		}
				
		try {
			template.query(sql.toString(), rag.toArray(), new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					Param param = buildParam(rs);
					paramList.add(param);
				}
			});
		} catch (Exception e) {
			LOGGER.error(StackTraceUtil.getStackTrace(e));
			throw new Exception(e);
		}
		return paramList;
	}
}